package edu.cuit.avatar.dao.impl;

import edu.cuit.avatar.common.JdbcHelper;
import edu.cuit.avatar.common.JdbcTemplate;
import edu.cuit.avatar.dao.DepartmentDao;
import edu.cuit.avatar.entity.Department;
import lombok.extern.slf4j.Slf4j;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 * @author <a href="mailto:1020zhaodan@163.com">Adan</a>
 * @version 1.0
 * @date 2025/7/25 15:36
 */
@Slf4j
public class DepartmentDaoImpl implements DepartmentDao {
    private JdbcHelper helper = JdbcHelper.getInstance();
    @Override
    public Integer save(Department dept) {
        var sql = "insert into dept(name,create_date,intro) values(?,?,?)";
        var conn = helper.getConnection();
        try {
            var ptst = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS); //返回主键
            ptst.setString(1, dept.getName());
            ptst.setObject(2, dept.getCreateDate());
            ptst.setString(3, dept.getIntro());

            ptst.executeUpdate();
            //获取自增的主键(key)
            ResultSet keys = ptst.getGeneratedKeys();
            return keys.next() ? keys.getInt(1) : null;
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
            throw new RuntimeException(e);
        } finally {
            helper.free(conn);
        }
    }

    @Override
    public int removeById(Integer id) {
        var sql = "delete from dept where id=?";
//        var conn = helper.getConnection();
//        try {
//            var ptst = conn.prepareStatement(sql);
//            ptst.setInt(1, id);
//
//            return ptst.executeUpdate();
//        } catch (SQLException e) {
//            log.error(e.getMessage(), e);
//            throw new RuntimeException(e);
//        } finally {
//            helper.free(conn);
//        }
        return JdbcTemplate.TEMPLATE.executeUpdate(sql, id);
    }

    @Override
    public int updateById(Department dept) {
        var sql = "update dept set name=?, create_date=?, intro=? where id=?";
        var conn = helper.getConnection();
        try {
            var ptst = conn.prepareStatement(sql);
            ptst.setString(1, dept.getName());
            ptst.setObject(2, dept.getCreateDate());
            ptst.setString(3, dept.getIntro());
            ptst.setInt(4, dept.getId());

            return ptst.executeUpdate();
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
            throw new RuntimeException(e);
        } finally {
            helper.free(conn);
        }
    }

    @Override
    public Department findById(Integer id) {
        Department dept = null;
        var sql = "select id, name, create_date, intro from dept where id=?";
        var conn = helper.getConnection();
        try {
            var ptst = conn.prepareStatement(sql);
            ptst.setInt(1, id);

            ResultSet rs = ptst.executeQuery();
            if(rs.next()){
                dept = new Department();
                dept.setId(rs.getInt("id"));
                dept.setName(rs.getString("name"));
                var date = rs.getDate("create_date");
                if(date != null) dept.setCreateDate(date.toLocalDate());
                dept.setIntro(rs.getString("intro"));
            }
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
            throw new RuntimeException(e);
        } finally {
            helper.free(conn);
        }
        return dept;
    }

    @Override
    public List<Department> limit(int offset, int size) {
        List<Department> departments = new ArrayList<>();
        var sql = "select id, name, create_date, intro from dept limit ?,?";
        var conn = helper.getConnection();
        try {
            var ptst = conn.prepareStatement(sql);
            ptst.setInt(1, offset);
            ptst.setInt(2, size);

            ResultSet rs = ptst.executeQuery();
            while(rs.next()){
                var dept = new Department();
                dept.setId(rs.getInt("id"));
                dept.setName(rs.getString("name"));
                var date = rs.getDate("create_date");
                if(date != null) dept.setCreateDate(date.toLocalDate());
                dept.setIntro(rs.getString("intro"));
                departments.add(dept);
            }
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
            throw new RuntimeException(e);
        } finally {
            helper.free(conn);
        }
        return departments;
    }

    @Override
    public long count() {
        var sql = "select count(*) from dept";
        var conn = helper.getConnection();
        try {
            var ptst = conn.prepareStatement(sql);
            var rs = ptst.executeQuery();
            return rs.next() ? rs.getLong(1) : 0;
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
            throw new RuntimeException(e);
        } finally {
            helper.free(conn);
        }
    }

    @Override
    public long count(String deptName) {
        var sql = "select count(*) from dept where name like ?";
        var conn = helper.getConnection();
        try {
            var ptst = conn.prepareStatement(sql);
            ptst.setString(1, "%" + deptName + "%");
            var rs = ptst.executeQuery();
            return rs.next() ? rs.getLong(1) : 0;
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
            throw new RuntimeException(e);
        } finally {
            helper.free(conn);
        }
    }

    @Override
    public List<Department> limit(int offset, int size, String deptName) {
        List<Department> departments = new ArrayList<>();
        var sql = "select id, name, create_date, intro from dept where name like ? limit ?,?";
        var conn = helper.getConnection();
        try {
            var ptst = conn.prepareStatement(sql);
            ptst.setString(1, "%" + deptName + "%");
            ptst.setInt(2, offset);
            ptst.setInt(3, size);

            var rs = ptst.executeQuery();
            while(rs.next()){
                var dept = new Department();
                dept.setId(rs.getInt("id"));
                dept.setName(rs.getString("name"));
                var date = rs.getDate("create_date");
                if(date != null) dept.setCreateDate(date.toLocalDate());
                dept.setIntro(rs.getString("intro"));
                departments.add(dept);
            }
        } catch (SQLException e) {
            log.error(e.getMessage(), e);
            throw new RuntimeException(e);
        } finally {
            helper.free(conn);
        }
        return departments;
    }
}
